<?php

namespace App\Http\Controllers\Excel;

use PHPExcel;
use PHPExcel_Style_Border;
use PHPExcel_IOFactory;
use App\Model\Orders\Order;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class ExcelController extends Controller
{
    //导出excel，教学部门
    public function excelFile_teaching($Starttime, $Endtime)
    {
        $PHPExcel = new PHPExcel();

        $Map['user_id'] = $_SESSION['Login_name'];
        $Where['Starttime'] = array('between',array($Starttime, $Endtime));
        $Where['Endtime'] = array('between',array($Starttime, $Endtime));
        $Where['_logic'] = 'or';
        $Map['_complex'] = $Where;
        $Order = new Order();
        $Result = $Order->where($Map)->orderBy('Starttime')->select();

        //设置列宽
        $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(1.38);
        $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15.38);
        $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(16);
        $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(6.25);
        $PHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(8.63);
        $PHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(28);
        $PHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(6);
        $PHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(7.88);
        $PHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(32);

        $PHPExcel->getProperties()->setCreator("iMax")
            ->setLastModifiedBy("iMax")
            ->setTitle("汇总");

        $PHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1','此')
            ->setCellValue('A2','处')
            ->setCellValue('A3','为')
            ->setCellValue('A4','装')
            ->setCellValue('A5','订')
            ->setCellValue('A6','线');

        //设置标题
        //合并单元格
        $PHPExcel->getActiveSheet()
            ->mergeCells('C1:P1');
        //设置字体大小
        $PHPExcel->getActiveSheet()->getStyle('C1:P1')->getFont()->setSize(16);
        //居中
        $PHPExcel->getActiveSheet()->getStyle('C1:P1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $PHPExcel->getActiveSheet()->getStyle('C1:P1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //设置标题
        $PHPExcel->setActiveSheetIndex(0)->setCellValue('C1',$_SESSION['Login_name'].'学院教学用车情况登记表（报账用）');

        $PHPExcel->getActiveSheet()->mergeCells('C2:D2');
        $PHPExcel->getActiveSheet()->getStyle()->getFont()->setSize(11);
        $PHPExcel->setActiveSheetIndex(0)->setCellValue('C2', '用车部门（盖章）:');

        $PHPExcel->setActiveSheetIndex(0)
            ->setCellValue('C3', '用车时间')
            ->setCellValue('D3', '往返地点')
            ->setCellValue('E3', '车型')
            ->setCellValue('F3', '车辆数')
            ->setCellValue('G3', '车牌号')
            ->setCellValue('H3', '车辆所有单位（公司）')
            ->setCellValue('I3', '单价')
            ->setCellValue('J3', '预计公里数')
            ->setCellValue('K3', '租(用)车费')
            ->setCellValue('L3', '用车专业班级')
            ->setCellValue('M3', '人数')
            ->setCellValue('N3', '实习(参观）单位')
            ->setCellValue('O3', '实习(参观）内容')
            ->setCellValue('P3', '带队教师签字')
            ->setCellValue('Q3', '备注');

        //组数
        $i = 0;
        $l = 4;
        $c = 2;
        for($i=0;$i<count($Result);$i++,$l++)
        {
            $PHPExcel->setActiveSheetIndex(0)
                ->setCellValue('C'.$l, strtok($Result[$i]['starttime'], " ").'---'.strtok($Result[$i]['endtime'], " "))
                ->setCellValue('D'.$l, $Result[$i]['route'])
                ->setCellValue('E'.$l, '30座:' . $Result[$i]['bus30'] . ',47座:' . $Result[$i]['bus47'])
                ->setCellValue('F'.$l, $Result[$i]['num'])
                ->setCellValue('G'.$l, $Result[$i]['license'])
                ->setCellValue('H'.$l, $Result[$i]['service_name'])
                ->setCellValue('I'.$l, $Result[$i]['unit_price'])
                ->setCellValue('J'.$l, $Result[$i]['kilometers'])
                ->setCellValue('K'.$l, $Result[$i]['total'])
                ->setCellValue('L'.$l, $Result[$i]['class'])
                ->setCellValue('M'.$l, $Result[$i]['num_people'])
                ->setCellValue('N'.$l, $Result[$i]['view_company'])
                ->setCellValue('O'.$l, $Result[$i]['view_content'])
                ->setCellValue('Q'.$l, $Result[$i]['ps']);

            $PHPExcel->getActiveSheet()->getRowDimension($l)->setRowHeight(45);
        }
        $PHPExcel->getActiveSheet()->getRowDimension($l)->setRowHeight(45);
        //设置垂直居中
        $PHPExcel->getActiveSheet()->getStyle('C3:Q'.$l)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $PHPExcel->getActiveSheet()->getStyle('C3:Q'.$l)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //自动换行
        $PHPExcel->getActiveSheet()->getStyle('A3:Q'.$l)->getAlignment()->setWrapText(true);

        $PHPExcel->setActiveSheetIndex(0)
            ->setCellValue('C'.$l, '合计')
            ->setCellValue('F'.$l, '=SUM(F4:F'.($l-1).')')
            ->setCellValue('K'.$l, '=SUM(K4:K'.($l-1).')')
            ->setCellValue('M'.$l, '=SUM(M4:M'.($l-1).')');
        //边框设定
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    'color' => array('rgb' => '000000'),//黑色
                ),
            ),
        );
        //边框绘制
        $PHPExcel->getActiveSheet()->getStyle('C3:Q'.$l)->applyFromArray($styleArray);

        $l++;
        $PHPExcel->getActiveSheet()->getStyle('A'.$l.':Q'.$l)->getFont()->setSize(12);
        $PHPExcel->setActiveSheetIndex(0)
            ->setCellValue('C'.$l, '系主任签字:')
            ->setCellValue('G'.$l, '主管教学院长签字:')
            ->setCellValue('N'.$l, '学院院长签字:');
        $PHPExcel->getActiveSheet()->getRowDimension($l)->setRowHeight(24);

        $l++;
        $PHPExcel->getActiveSheet()->getStyle('A'.$l.':Q'.($l+2))->getFont()->setSize(11);
        $PHPExcel->setActiveSheetIndex(0)
            ->setCellValue('C'.$l, '签字日期:')
            ->setCellValue('G'.$l, '签字日期:')
            ->setCellValue('N'.$l, '签字日期:');
        $PHPExcel->getActiveSheet()->getRowDimension($l)->setRowHeight(24);

        $l++;
        $PHPExcel->setActiveSheetIndex(0)
            ->setCellValue('C'.$l, '注: 1.合计只针对车辆数、租车费和学生人数进行');
        $PHPExcel->getActiveSheet()->getRowDimension($l)->setRowHeight(24);

        $l++;
        $PHPExcel->setActiveSheetIndex(0)
            ->setCellValue('C'.$l, '    2.请提供实习学生名单');
        $PHPExcel->getActiveSheet()->getRowDimension($l)->setRowHeight(24);

        $Writer = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel2007');

        $Writer->save('Excel/1.xlsx');
    }
}
